﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using UniMana.Data;
using UniMana.Utils.Configs;

namespace UniMana.Data
{
    public static class Personnel_PersonnelData
    {
        #region [Tân]
        public static IQueryable GetPersonelListByCodition(this Table<Personnel_Personnel> tPersonel,
    Table<Catalogue_Department> tDepartment, Table<Catalogue_Position> tPosition, int deparmentKey = -1, int persTypeKey = -1,
            int teach = -1)
        {
            var pl = from p in tPersonel
                     join p1 in tDepartment on p.Catalogue_DepartmentKey equals p1.Catalogue_DepartmentKey
                     join p2 in tPosition on p.Catalogue_PositionKey equals p2.Catalogue_PositionKey
                     select new
                     {
                         p.Personnel_PersonnelKey,
                         p.ID,
                         p.LastName,
                         p.Name,
                         p.Birthday,
                         p.ExceptionBirthday,
                         Bday = p.Birthday.HasValue ? Convert.ToDateTime(p.Birthday.Value.ToString()).ToShortDateString() : p.ExceptionBirthday,
                         p.PlaceOfBirth,
                         Gender = p.Gender == DefaultConfig.FEMALE ? "Nữ" : "Nam",
                         p.Catalogue_DegreeKey,
                         p.Catalogue_DepartmentKey,
                         p.Catalogue_PositionKey,
                         p.CellPhone,
                         DepartmentName = p1.Name,
                         PositionName = p2.Name,
                         p.Personnel_PersonnelTypeKey,
                         p.Teach
                     };

            //Lọc theo khoa
            if (deparmentKey != -1)
            {
                pl = pl.Where(d => d.Catalogue_DepartmentKey == deparmentKey);
            }
            //Lọc loại giảng viên
            if (persTypeKey != -1)
            {
                pl = pl.Where(p => p.Personnel_PersonnelTypeKey == persTypeKey);
            }

            //Lọc theo nhóm giảng viên có tham gia giảng dạy hay không
            if (teach != -1)
            {
                if (teach == 0)
                {
                    pl = pl.Where(p => p.Teach == false);
                }
                else
                {
                    pl = pl.Where(p => p.Teach == true);
                }
            }
            return pl;
        }
        #endregion

        #region [Chi]
        public static IQueryable GetPersonelDataDep(this  Table<Personnel_Personnel> Pernel,
                                                     Table<Catalogue_Degree> cataDeg,
                                                     Table<Catalogue_Department> cataDep,
                                                     Table<Catalogue_Position> cataPos,
                                                     int _iDep = 0)
        {
            var view1 = from t in Pernel
                        join t0 in cataDeg on new { Catalogue_DegreeKey = Convert.ToInt32(t.Catalogue_DegreeKey) } equals new { Catalogue_DegreeKey = t0.Catalogue_DegreeKey }
                        join t1 in cataPos on new { Catalogue_PositionKey = Convert.ToInt32(t.Catalogue_PositionKey) } equals new { Catalogue_PositionKey = t1.Catalogue_PositionKey }
                        join t2 in cataDep on new { Catalogue_DepartmentKey = Convert.ToInt32(t.Catalogue_DepartmentKey) } equals new { Catalogue_DepartmentKey = t2.Catalogue_DepartmentKey }
                        group new { t1, t0, t2 } by new
                        {
                            t1.Catalogue_PositionKey,
                            t1.Name,
                            t0.Catalogue_DegreeKey,
                            Column1 = t0.Name,
                            t2.Catalogue_DepartmentKey,
                            Column2 = t2.Name
                        } into g
                        orderby
                          
                          g.Key.Catalogue_PositionKey
                        select new
                        {
                            Catalogue_DepartmentKey = (System.Int32?)g.Key.Catalogue_DepartmentKey,
                            PhongBan = g.Key.Column2,
                            Catalogue_PositionKey = (System.Int32?)g.Key.Catalogue_PositionKey,
                            ChucVu = g.Key.Name,
                            Catalogue_DegreeKey = (System.Int32?)g.Key.Catalogue_DegreeKey,
                            TrinhDo = g.Key.Column1,
                            TongSoNV = (Int64?)g.Count()
                        };
            if (_iDep != 0)
            {
                view1 = view1.Where(p => p.Catalogue_DepartmentKey == _iDep);
            }
            return view1;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="Pernel"></param>
        /// <param name="cataDeg"></param>
        /// <param name="cataDep"></param>
        /// <param name="cataPos"></param>
        /// <param name="_iDep"></param>
        /// <returns></returns>
        public static IQueryable GetPersonelData(this  Table<Personnel_Personnel> Pernel,
                                                     Table<Catalogue_Degree> cataDeg,
                                                     Table<Catalogue_Department> cataDep,
                                                     Table<Catalogue_Position> cataPos)
                                                     
        {
            var view1 = from t in Pernel
                        join t0 in cataDeg on new { Catalogue_DegreeKey = Convert.ToInt32(t.Catalogue_DegreeKey) } equals new { Catalogue_DegreeKey = t0.Catalogue_DegreeKey }
                        join t1 in cataPos on new { Catalogue_PositionKey = Convert.ToInt32(t.Catalogue_PositionKey) } equals new { Catalogue_PositionKey = t1.Catalogue_PositionKey }
                        join t2 in cataDep on new { Catalogue_DepartmentKey = Convert.ToInt32(t.Catalogue_DepartmentKey) } equals new { Catalogue_DepartmentKey = t2.Catalogue_DepartmentKey }
                        group new { t1, t0, t2 } by new
                        {
                            t1.Catalogue_PositionKey,
                            t1.Name,
                            t0.Catalogue_DegreeKey,
                            Column1 = t0.Name,
                            t2.Catalogue_DepartmentKey,
                            Column2 = t2.Name
                        } into g
                        orderby

                          g.Key.Catalogue_PositionKey
                        select new
                        {
                            Catalogue_DepartmentKey = (System.Int32?)g.Key.Catalogue_DepartmentKey,
                            PhongBan = g.Key.Column2,
                            Catalogue_PositionKey = (System.Int32?)g.Key.Catalogue_PositionKey,
                            ChucVu = g.Key.Name,
                            Catalogue_DegreeKey = (System.Int32?)g.Key.Catalogue_DegreeKey,
                            TrinhDo = g.Key.Column1,
                            TongSoNV = (Int64?)g.Count()
                        };
            //if (_iDep != 0)
            //{
            //    view1 = view1.Where(p => p.Catalogue_DepartmentKey == _iDep);
            //}
            return view1;
        }
        #endregion

        #region [Chi]
        /// <summary>
        /// Lấy danh sách giảng viên phụ trách môn học
        /// </summary>
        /// <param name="personnel"></param>
        /// <param name="personnel_Subject"></param>
        /// <param name="subjectkey"></param>
        /// <returns></returns>
        public static IQueryable
            GetBySubject(this Table<Personnel_Personnel> personnel,
            Table<Personnel_Personnel_Subject> personnel_Subject,
            int subjectkey)
        {
            var IQuery = from psj in personnel_Subject
                         join ps in personnel on psj.Personnel_PersonnelKey.ToString() equals ps.Personnel_PersonnelKey.ToString()
                         where
                           psj.Training_SubjectKey == subjectkey &&
                           psj.Status == true
                         select ps;
            return IQuery.Distinct();
        }
        /// <summary>
        /// lấy giảng viên theo lớp học phần
        /// </summary>
        /// <param name="DefaultKey"></param>
        public static IQueryable Get_ByKey(
            this Table<Personnel_Personnel> personnel,
            string DefaultKey = "")
        {
            var IQuery = personnel.Where(tb => tb.Personnel_PersonnelKey.ToString() == DefaultKey);
            return IQuery;
        }
        #endregion

        #region[An]
        /// <summary>
        /// Lấy danh sách thuộc phòng ban
        /// </summary>
        /// <param name="nPersonel"></param>
        /// <param name="_iDepartmentKey"></param>
        /// <returns></returns>

        public static IQueryable GetListByDepartment(this Table<Personnel_Personnel> nPersonel,
            int _iDepartmentKey)
        {
            var iQuery = from personel in nPersonel
                         select new
                         {
                             personel.Personnel_PersonnelKey,
                             personel.ID,
                             FullName = personel.LastName + " " + personel.Name,
                             personel.Catalogue_DepartmentKey
                         };
            if (_iDepartmentKey > 0)
            {
                iQuery = iQuery.Where(tb => tb.Catalogue_DepartmentKey == _iDepartmentKey);
            }
            return iQuery;
        }

        /// <summary>
        /// Lấy danh sách nhân viên với mã khoa
        /// </summary>
        /// <param name="nPersonnel_Personnel"></param>
        /// <param name="m_iDepartmentKey"></param>
        /// <returns></returns>

        public static IQueryable GetPersonnalWithDepartmentKey(this Table<Personnel_Personnel> nPersonnel_Personnel,
            int m_iDepartmentKey)
        {
            var values = from t in nPersonnel_Personnel
                         select t;

            if (m_iDepartmentKey > 0)
                values = values.Where(tb => tb.Catalogue_DepartmentKey == m_iDepartmentKey);

            return values;
        }

        /// <summary>
        /// Lấy danh sách personnal như là người yêu cầu
        /// </summary>
        /// <param name="nPersonnel_Personnel"></param>
        /// <returns></returns>
        public static IQueryable GetPersonnalAsRequireUser(this Table<Personnel_Personnel> nPersonnel_Personnel)
        {
            var values = from t in nPersonnel_Personnel
                         where t.ID != null
                         select new
                         {
                             RequireUser = t.Personnel_PersonnelKey,
                             t.ID,
                             t.LastName,
                             t.Name,
                             FullName = t.LastName + " " + t.Name
                         };
            return values.Distinct();
        }

        public static IQueryable GetPersonnelKeyIDLastNameName(this Table<Personnel_Personnel> nPersonnel_Personnel)
        {
            var value = from p in nPersonnel_Personnel
                        where p.ID != null
                        select new
                        {
                            p.Personnel_PersonnelKey,
                            p.ID,
                            p.LastName,
                            p.Name,
                            FullName = p.LastName + " " + p.Name
                        };
            return value.Distinct();
        }

        /// <summary>
        /// Danh sách giảng viên có thể giảng
        /// </summary>
        /// <param name="nPersonnel_Personnel"></param>
        /// <returns></returns>

        public static IQueryable GetListLecturer(this Table<Personnel_Personnel> nPersonnel_Personnel)
        {
            var values = from t in nPersonnel_Personnel
                         where t.Teach == true
                         select new
                         {
                             t.Personnel_PersonnelKey,
                             t.ID,
                             t.LastName,
                             t.Name,
                             FullName = t.LastName + " " + t.Name
                         };
            return values;
        }

        /// <summary>
        /// GetData
        /// </summary>
        /// <param name="nPersonnel_Personnel"></param>
        /// <returns></returns>

        public static IQueryable GetFullName(this Table<Personnel_Personnel> nPersonnel_Personnel)
        {
            var values = from t in nPersonnel_Personnel
                         select new
                         {
                             t.Personnel_PersonnelKey,
                             t.LastName,
                             t.Name,
                             FullName = t.LastName + " " + t.Name
                         };
            return values.Distinct();
        }
        #endregion

        #region [Hòa]
        ///
        /// <summary>
        /// Lấy danh sách chưa được phân công nhập
        /// </summary>
        /// <param name="nPersonnel_Personnel"></param>
        /// <param name="nTraining_Score_TaskInput"></param>
        /// <param name="nSystem_User"></param>
        /// <param name="nSystem_User_Rule"></param>
        /// <param name="nSystem_Rule"></param>
        /// <param name="_iTraining_Subject_ClassKey">Mã lớp học phần</param>
        /// <param name="_bInput">True là nhập điển, False là kiểm tra điểm</param>
        /// <returns></returns>
        public static IQueryable GetListNotTaskInputScore(this Table<Personnel_Personnel> nPersonnel_Personnel,
            Table<Training_Score_TaskInput> nTraining_Score_TaskInput,
            Table<System_User> nSystem_User,
            Table<System_User_Rule> nSystem_User_Rule,
            Table<System_Rule> nSystem_Rule,
            int _iTraining_Subject_ClassKey,
            Guid personelKey, bool _bInput = true)
        {
            string strRequest;
            if (_bInput)
            {
                strRequest = RuleConfig.SCORETRANSCRIPTS;
            }
            else
            {
                strRequest = RuleConfig.SCORETRANSCRIPTS;
            }
            var personel = from user in nSystem_User
                           from rule in nSystem_Rule
                           from userrule in nSystem_User_Rule
                           where rule.RuleID == strRequest
                           where userrule.System_RuleKey == rule.System_RuleKey
                           where user.System_UserKey == userrule.System_UserKey
                           where userrule.AllowAccess == true
                           select user.Personnel_PersonnelKey;

            var iInTask = from t in nTraining_Score_TaskInput
                          join t0 in nPersonnel_Personnel on t.Personnel_PersonnelKey equals t0.Personnel_PersonnelKey
                          where
                            t.Training_Subject_ClassKey == _iTraining_Subject_ClassKey
                            && t.Input == _bInput
                            && (t.Lock == null || t.Lock == false)
                          select
                              t.Personnel_PersonnelKey.Value;

            var iQuery = from personnel in nPersonnel_Personnel
                         where
                            !iInTask.Contains(personnel.Personnel_PersonnelKey)
                         select new
                         {
                             personnel.Personnel_PersonnelKey,
                             personnel.ID,
                             personnel.LastName,
                             personnel.Name
                         };
            iQuery = from iquery in iQuery
                     where personel.Contains(iquery.Personnel_PersonnelKey)
                     select iquery;

            if (personelKey != null)
            {
                iQuery = from query in iQuery
                         where query.Personnel_PersonnelKey != personelKey
                         select query;
            }
            if (_bInput)
                return iQuery;
            else
            {

                var inputScore = from iquery in nTraining_Score_TaskInput
                                 where iquery.Input == true
                                 where iquery.Training_Subject_ClassKey == _iTraining_Subject_ClassKey
                                 select iquery.Personnel_PersonnelKey;

                iQuery = from iquery in iQuery
                         where !(inputScore.Contains(iquery.Personnel_PersonnelKey))
                         select iquery;
                return iQuery;
            }
        }

        /// <summary>
        /// Lấy danh sách chưa được phân công nhập
        /// </summary>
        /// <param name="nPersonnel_Personnel"></param>
        /// <param name="nTraining_Score_TaskInput"></param>
        /// <param name="nSystem_User"></param>
        /// <param name="nSystem_User_Rule"></param>
        /// <param name="nSystem_Rule"></param>
        /// <param name="_lClassKeys">Danh sách mã lớp</param>
        /// <param name="_dteNow">Thời gian hiện tại</param>
        /// <param name="_bInput">True là nhập điển, False là kiểm tra điểm</param>
        /// <returns></returns>
        public static IQueryable GetListNotTaskInputScore(this Table<Personnel_Personnel> nPersonnel_Personnel,
            Table<Training_Score_TaskInput> nTraining_Score_TaskInput,
            Table<System_User> nSystem_User,
            Table<System_User_Rule> nSystem_User_Rule,
            Table<System_Rule> nSystem_Rule,
            List<int> _lClassKeys,
            DateTime _dteNow,
            Guid personelKey,
            bool _bInput = true)
        {
            string strRequest;
            if (_bInput)
            {
                strRequest = Utils.Configs.RuleConfig.SCORETRANSCRIPTS;
            }
            else
            {
                strRequest = Utils.Configs.RuleConfig.SCORETRANSCRIPTS;
            }
            var personel = from user in nSystem_User
                           from rule in nSystem_Rule
                           from userrule in nSystem_User_Rule
                           where rule.RuleID == strRequest
                           where userrule.System_RuleKey == rule.System_RuleKey
                           where user.System_UserKey == userrule.System_UserKey
                           where userrule.AllowAccess == true
                           select user.Personnel_PersonnelKey;

            var iInTask = from t in nTraining_Score_TaskInput
                          join t0 in nPersonnel_Personnel on t.Personnel_PersonnelKey equals t0.Personnel_PersonnelKey
                          where
                              t.Date.Value.AddDays(t.Period.Value) > _dteNow
                              && t.Input == _bInput
                              && (t.Lock != null && t.Lock == false)
                          select new
                          {
                              t.Training_Subject_ClassKey,
                              t.Personnel_PersonnelKey
                          };
            for (int i = 0; i < _lClassKeys.Count; i++)
            {
                iInTask = iInTask.Where(p => p.Training_Subject_ClassKey == _lClassKeys[i]);
                if (i == _lClassKeys.Count - 1)
                    break;
            }
            var iInTask1 = from t in iInTask
                           select
                            t.Personnel_PersonnelKey;

            List<Guid?> lGuid = iInTask1.ToList();

            var iQuery = from personnel in nPersonnel_Personnel
                         where
                            !lGuid.Contains(personnel.Personnel_PersonnelKey)
                         select new
                         {
                             personnel.Personnel_PersonnelKey,
                             personnel.ID,
                             personnel.LastName,
                             personnel.Name
                         };
            iQuery = from iquery in iQuery
                     where personel.Contains(iquery.Personnel_PersonnelKey)
                     select iquery;
            iQuery = iQuery.Where(p => p.Personnel_PersonnelKey != personelKey);
            return iQuery;
        }

        /// <summary>
        /// Lấy danh sách thuộc phòng ban
        /// </summary>
        /// <param name="nPersonel"></param>
        /// <param name="_iDepartmentKey"></param>
        /// <returns></returns>

        public static IQueryable GetListByDepartment1(this Table<Personnel_Personnel> nPersonel,
            int _iDepartmentKey)
        {
            var iQuery = from personel in nPersonel
                         select new
                         {
                             personel.Personnel_PersonnelKey,
                             personel.ID,
                             FullName = personel.LastName + " " + personel.Name,
                             personel.Catalogue_DepartmentKey
                         };
            if (_iDepartmentKey > 0)
            {
                iQuery = iQuery.Where(tb => tb.Catalogue_DepartmentKey == _iDepartmentKey);
            }
            return iQuery;
        }

        public static IQueryable GetListTeacher(
            this Table<Personnel_Personnel> nPersonnel_Personnel,
            List<Guid> _lgListPersonel = null)
        {
            var iQuery = from personel in nPersonnel_Personnel
                         //where personel.Teach == true
                         select new
                         {
                             personel.Personnel_PersonnelKey,
                             personel.ID,
                             FullName = personel.LastName + " " + personel.Name,
                             personel.Catalogue_DepartmentKey,
                             personel.DegreedOn,
                             personel.YearOfStartTeach
                         };
            if (_lgListPersonel != null)
            {
                iQuery = from query in iQuery
                         where _lgListPersonel.Contains(query.Personnel_PersonnelKey)
                         select query;
            }
            return iQuery;
        }

        public static IQueryable GetListTeacher(
            this Table<Personnel_Personnel> nPersonnel_Personnel,
            Table<Personnel_Personnel_Session> nPersonnel_Session,
            Table<Personnel_Personnel_SubjectRegist> nPersonnel_SubjectRegist,
            int session = -1,
            int weekday = -1,
            DateTime StartTime = new DateTime(),
            DateTime EndTime = new DateTime(),
            int subject = -1,
            List<Guid> _lgListPersonel = null)
        {
            var iQuery = from p in nPersonnel_Personnel
                         join p_ss in nPersonnel_Session
                            on p.Personnel_PersonnelKey equals p_ss.Personnel_PersonnelKey
                         join p_sj_re in nPersonnel_SubjectRegist
                            on p.Personnel_PersonnelKey equals p_sj_re.Personnel_PersonnelKey
                         where p.Teach == true &&
                                p_sj_re.Status == true &&
                                p_ss.Personnel_WorkTypeKey == 1 &&
                                p_ss.RegistTime >= StartTime &&
                                p_ss.RegistTime <= EndTime
                         select new
                         {
                             p.Personnel_PersonnelKey,
                             p.ID,
                             FullName = p.LastName + " " + p.Name,
                             p_sj_re.StartTime,
                             p_sj_re.EndTime,
                             p_ss.Training_SessionKey,
                             p_ss.Training_WeekDayKey,
                             p_sj_re.Training_SubjectKey
                         };
            if (_lgListPersonel != null)
            {
                iQuery = from query in iQuery
                         where _lgListPersonel.Contains(query.Personnel_PersonnelKey)
                         select query;
            }
            if (session != -1 && weekday != -1)
            {
                iQuery = from Q in iQuery
                         where Q.Training_SessionKey == session &&
                               Q.Training_WeekDayKey == weekday
                         select Q;
            }
            if (StartTime != new DateTime() && EndTime != new DateTime())
            {
                iQuery = from Q in iQuery
                         where Q.StartTime <= StartTime &&
                               Q.EndTime >= EndTime
                         select Q;
            }
            if (subject != -1)
            {
                iQuery = from Q in iQuery
                         where Q.Training_SubjectKey == subject
                         select Q;
            }
            var _iQuery = from Q in iQuery
                          select new
                          {
                              Q.Personnel_PersonnelKey,
                              Q.ID,
                              Q.FullName
                          };
            return _iQuery;
        }
        #endregion
    }
}
